﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'P_DeleteBasicReference_BasicReferences')
	BEGIN
		DROP  Procedure  esf_sso.P_DeleteBasicReference_BasicReferences
	END
GO

CREATE Procedure esf_sso.P_DeleteBasicReference_BasicReferences
	@pBasicReferenceID BIGINT
	,@pChildrenBasicReferenceTypeID		INT = NULL
	,@pBigParentBasicReferenceID		INT = NULL
AS
	
	IF @pBigParentBasicReferenceID IS NULL
	BEGIN

		DELETE FROM 
			esf_sso.TreeBasicReference 
		WHERE 
			ParentBasicReferenceID = @pBasicReferenceID
			AND (@pChildrenBasicReferenceTypeID IS NULL 
				OR BasicReferenceID 
					IN (SELECT BasicReferenceID FROM esf_sso.TreeBasicReference 
					INNER JOIN esf_sso.BasicReference BR ON BR.ID = BasicReferenceID
					WHERE 
						ParentBasicReferenceID = @pBasicReferenceID
						AND BR.BasicReferenceTypeID = @pChildrenBasicReferenceTypeID))
	END
	ELSE
	BEGIN
		
		DELETE FROM 
			esf_sso.TreeBasicReference 
		WHERE 
			ParentBasicReferenceID = @pBasicReferenceID
			AND BasicReferenceID IN (SELECT ID FROM esf_sso.BasicReference WHERE Code LIKE (SELECT TOP 1 Code FROM esf_sso.BasicReference WHERE ID = @pBigParentBasicReferenceID) + '%')
			AND (@pChildrenBasicReferenceTypeID IS NULL 
				OR BasicReferenceID 
					IN (SELECT BasicReferenceID FROM esf_sso.TreeBasicReference 
					INNER JOIN esf_sso.BasicReference BR ON BR.ID = BasicReferenceID
					WHERE 
						ParentBasicReferenceID = @pBasicReferenceID
						AND BR.BasicReferenceTypeID = @pChildrenBasicReferenceTypeID))

	END

GO